﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Text;
using System.IO;

namespace CrmApplication.Models
{
	public class MailerModels
	{

		#region RegionMappingMailer

		public decimal IdMailer{get;set;}
		public string MailerName { get; set; }

		public decimal IdCampaign{get;set;}
		public string IdCampaign_val { get; set; }
		public string CampaignName { get; set; }
		
		public decimal Status{get;set;}
		public decimal SendSuccess{get;set;}
		public decimal SendFailed{get;set;}

		public DateTime StartDate { get; set; }
		public string StartDateL { get; set; }
		
		//public OracleBlob MailerBody { get; set; }
		public byte[] MailerBody { get; set; }
		public string MailerBodyL { get; set; }
		public OracleBlob MailerBodyB { get; set; }
		
		#endregion

		#region RegionOracleConnect
		private static OracleConnection connect;

       /// <summary>
       /// database connection string
       /// </summary>
        static MailerModels()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }
		#endregion

		#region RegionMailer
		/// <summary>
		/// select all MailerModelss from database
		/// </summary>
		/// <returns></returns>
		public static List<MailerModels> GetMailer()
        {
			string selectString = "select m.id_mailer,m.mailer_name,m.id_campaign,to_char(m.start_date,'YYYY-MM-DD HH24:MI:SS'),"
									+ "m.status,m.send_success,m.send_failed,c.campaign_name,m.start_date "
									+ " from mailer m "
									+ " join campaign c on m.id_campaign=c.id_campaign "
									+ " order by m.mailer_name, c.campaign_name ";            

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

			List<MailerModels> result = new List<MailerModels>();
			MailerModels result1 = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new MailerModels();
                        result1.IdMailer = sdr.GetDecimal(0);
                        result1.MailerName = sdr.GetString(1);
						result1.IdCampaign = sdr.GetDecimal(2);
						result1.StartDateL = sdr.GetString(3);
						result1.Status = sdr.GetDecimal(4);
						result1.SendSuccess = sdr.GetDecimal(5);
						result1.SendFailed = sdr.GetDecimal(6);
						result1.CampaignName = sdr.GetString(7);
						result1.StartDate = sdr.GetDateTime(8);
						result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

		/// <summary>
		/// select infos for current selected mailer
		/// </summary>
		/// <param name="idMailer"></param>
		/// <returns></returns>
		public static List<MailerModels> GetMailerById (decimal idMailer)
		{
			string selectString = "select m.id_mailer,m.id_campaign,m.mailer_name,to_char(m.start_date,'YYYY-MM-DD HH24:MI:SS'),m.status, " 
									+ " c.campaign_name, m.mailer_body "
									+ " from mailer m join campaign c on m.id_campaign=c.id_campaign "
									+ " where id_mailer= :idMailer";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idMailer", idMailer);
		
			List<MailerModels> result = new List<MailerModels>();
			MailerModels result1 = null;


			//FileStream fs;                          // Writes the BLOB to a file (*.bmp).
			//BinaryWriter bw;                        // Streams the BLOB to the FileStream object.
			int bufferSize =100;                   // Size of the BLOB buffer.
			byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
			long retval=0;                            // The bytes returned from GetBytes.
			long startIndex = 0;                    // The starting position in the BLOB output.			                    

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new MailerModels();
						result1.IdMailer = sdr.GetDecimal(0);
						result1.IdCampaign = sdr.GetDecimal(1);
						result1.MailerName = sdr.GetString(2);
						result1.StartDateL = sdr.GetString(3);						
						result1.Status = sdr.GetDecimal(4);
						result1.IdCampaign_val = sdr.GetString(5);

						//fs = new FileStream("test.txt",FileMode.OpenOrCreate, FileAccess.Write);
						//bw = new BinaryWriter(fs);

						// Reset the starting byte for the new BLOB.
						startIndex = 0;
						retval = sdr.GetBytes(6, startIndex, outbyte, 0, bufferSize);
						// Continue reading and writing while there are bytes beyond the size of the buffer.
						while (retval == bufferSize)
						{
							//bw.Write(outbyte);
							//bw.Flush();

							// Reposition the start index to the end of the last buffer and fill the buffer.
							startIndex += bufferSize;
							retval = sdr.GetBytes(6, startIndex, outbyte, 0, bufferSize);
						}

						// Write the remaining buffer.
						//bw.Write(outbyte, 0, (int)retval);
						//bw.Flush();

						// Close the output file.
						//bw.Close();
						//fs.Close();

						result1.MailerBodyL = Encoding.ASCII.GetString(outbyte);

						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// insert MailerModels
		/// </summary>
		/// <param name="MailerModels"></param>
		public static void AddMailer (MailerModels mailer)
		{
			string insert = "INSERT INTO mailer(mailer_name,start_date,id_campaign,mailer_body)" +
				"VALUES (:mailerName,:startDate,:idCampaign,:mailerBody)";

			OracleCommand usercomm = new OracleCommand(insert);
			usercomm.Parameters.Add("mailerName", mailer.MailerName);
			usercomm.Parameters.Add("startDate", mailer.StartDate);			
			usercomm.Parameters.Add("idCampaign", mailer.IdCampaign);
			usercomm.Parameters.Add("mailerBody", mailer.MailerBody);

			usercomm.Connection = connect;

			try
			{
				connect.Open();
				usercomm.ExecuteNonQuery();
			}
			finally
			{
				connect.Close();
			}
		}

		/// <summary>
		/// update MailerModels
		/// </summary>
		/// <param name="MailerModels"></param>
		/// <returns></returns>
		public static MailerModels EditMailer (MailerModels mailer)
		{
			string selectString = "update mailer set mailer_name=:mailerName,id_campaign=:idCampaign,start_date=:startDate, " +
									"status=:status,mailer_body=:mailerBody where id_mailer=:idMailer";

			OracleCommand updateComm = new OracleCommand(selectString);
			updateComm.Connection = connect;
			updateComm.Parameters.Add(":mailerName", mailer.MailerName);
			updateComm.Parameters.Add(":idCampaign", mailer.IdCampaign);
			updateComm.Parameters.Add(":startDate", mailer.StartDate);
			updateComm.Parameters.Add(":status", mailer.Status);
			updateComm.Parameters.Add(":mailerBody", mailer.MailerBody);
			updateComm.Parameters.Add(":idMailer", mailer.IdMailer);

			MailerModels result = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = updateComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result = new MailerModels();						
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}
		#endregion

		#region RegionArrayJson
		/// <summary>
		/// create MailerModels Json array
		/// </summary>
		/// <returns></returns>
		public static MailerContainer GetMailerArray ()
		{
			MailerModels[] data = new MailerModels[20];
			data = MailerModels.GetMailer().ToArray();

			MailerContainer container = new MailerContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create Json array for selected mailer(by mailer id)
		/// </summary>
		/// <param name="idUser"></param>
		/// <returns></returns>
		public static MailerContainer GetMailerArrayById (decimal idMailer)
		{
			MailerModels[] data = new MailerModels[20];
			data = MailerModels.GetMailerById(idMailer).ToArray();

			MailerContainer container = new MailerContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}
		#endregion
	}

	public class MailerContainer
	{
		public MailerModels[] Records { get; set; }
		public int Count { get; set; }
	}

}